﻿using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NB.MGJ.Common.Helper;
using NB.MGJ.Common.Utility;
using MySql.Data.MySqlClient;
using NB.MGJ.Model.CodeManage;
using L.EntityFramework;

namespace NB.MGJ.Service
{
    public partial class ManagerService
    {
        
        #region 角色操作
        /// <summary>
        /// 查询角色
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public PagedList<V_Role> GetRoleInfoService(DetailSearch Smodel, ref PagerModel pager)
        {

            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append(" ");
            strSql1.Append("ID,");
            strSql1.Append("RoleName,");
            strSql1.Append("RoleType,");
            strSql1.Append("EnterpriseName,");
            strSql1.Append("ChangeDate,");
            strSql1.Append("Remark,");
            var strSql = new StringBuilder(strSql1.ToString().Trim(','));
            pager.FldName = strSql.ToString();
            pager.TblName = " V_Role ";

            string StrCondition = "   1=1 ";

            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "   1=1 and ViewDel=0 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }

            if (Smodel.EnterpriseName != null)
            {
                StrCondition += "  and (EnterpriseName like '%" + Smodel.EnterpriseName + "%')";
            }
            if (Smodel.RoleName != null)
            {
                StrCondition += " and (RoleName like '%" + Smodel.RoleName + "%')";
            }
            if (Smodel.RoleType == "0" || Smodel.RoleType == "1")
            {
                StrCondition += " and (RoleType like '%" + Smodel.RoleType + "%')";
            }

            { Smodel.ChangeDate = DateTime.MinValue; };
            if (Smodel.StartDate != "" && Smodel.StartDate != null)
            {
                StrCondition += " and  (left(ChangeDate,10) >= '" + Smodel.StartDate + "')";
            }
            if (Smodel.EndDate != "" && Smodel.EndDate != null)
            {
                StrCondition += " and  (left(ChangeDate,10) <= '" + Smodel.EndDate + "')";
            }
            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort))
                pager.FldSort = " id desc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<V_Role> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_Role>("Common_PageList", parameters);
            return new PagedList<V_Role>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));

        }

        /// <summary>
        /// 获取当前机构全部角色
        /// where RoleID = 修改为 where RoleID in
        /// </summary>
        /// <param name="eid"></param>
        /// <returns></returns>
        public List<V_Role> GetRoleAllService(int eid, string roleid)
        {

            //string sql = " select ButtonId,ViewDel,ViewFlag,EnterpriseId,RoleName,Remark,RoleType,OrganizationTypes,EnterpriseName from V_Role  where EnterpriseId=" + eid+" or RoleType=2"; 
            string sql = @"select * from v_role where EnterpriseId={0} or  ID in ( select roleID from sys_VersionsRole where VersionsRoleID in ({1}))";
            sql = string.Format(sql, eid, roleid);
            List<V_Role> list = _baseRepository.ExecuteSqlCollection<V_Role>(sql).ToList();
            return list;
        }
        public List<V_Role> GetRoleAllService(int eid, int uId)
        {

            string sql = " select ID,ViewDel,ViewFlag,EnterpriseId,RoleName,Remark,RoleType,OrganizationTypes,EnterpriseName from V_Role  where EnterpriseId=" + eid + " and ChangeLoginID=" + uId;

            List<V_Role> list = _baseRepository.ExecuteSqlCollection<V_Role>(sql).ToList();
            return list;
        }

        #region 导出查询
        public IEnumerable<Sys_Role> GetAllRole(string key)
        {
            var rs = "SELECT * FROM Sys_Role ";
            if (!string.IsNullOrWhiteSpace(key))
            {
                rs += " where  ViewDel=0 and id In(" + key + ")";
            }
            return _baseRepository.ExecuteSqlCollection<Sys_Role>(rs);
        }

        #endregion

        /// <summary>
        /// 角色删除
        /// Pass 1
        /// </summary>
        public int DeleteRoleInfoService(int id)
        {
            //string sql = "select * from Sys_Role where ButtonId=" + id;
            //Sys_Role _query = _baseRepository.ExecuteSqlCollection<Sys_Role>(sql).FirstOrDefault();
            //_query.ViewDel = 1;
            //int result = _baseRepository.Update<Sys_Role>(_query);
            //return result;
            string _sql = "UPDATE  Sys_Role SET ViewDel = 1 WHERE ID = " + id;
            return _baseRepository.ExecuteSql<int>(_sql);
        }

        /// <summary>
        /// 角色新增
        /// </summary>
        /// <param name="Role"></param>
        /// <returns></returns>
        public int AddRoleInfoService(Sys_Role Role)
        {
            int result = _baseRepository.Insert<Sys_Role>(Role);
            return result;
        }

        /// <summary>
        /// 角色编辑
        /// </summary>
        /// <param name="Role"></param>
        /// <returns></returns>
        public int UpdateRoleInfoService(Sys_Role Role)
        {

            int result = _baseRepository.Update<Sys_Role>(Role);
            return result;
        }
        /// <summary>
        /// 获取所有的机构
        /// </summary>
        /// <returns></returns>
        public List<Organization_Enterprise> GetOrganizationService()
        {
            string sql = "select ID,EnterpriseName from Organization_Enterprise where ViewDel=0";
            List<Organization_Enterprise> _query = _baseRepository.ExecuteSqlCollection<Organization_Enterprise>(sql).ToList();
            return _query;
        }

        /// <summary>
        /// 原：_baseRepository.ExecuteSqlCollection<Sys_Role>(sql).ToList().FirstOrDefault();
        /// 修改:_baseRepository.ExecuteSqlCollection<Sys_Role>(sql).FirstOrDefault();
        /// Pass 1
        /// </summary>
        public Sys_Role GetRoleToIdService(int id)
        {
            string sql = "select * from Sys_Role where  id=" + id;
            Sys_Role _query = _baseRepository.ExecuteSqlCollection<Sys_Role>(sql).FirstOrDefault();
            return _query;
        }

        #endregion

        #region 菜单操作
        /// <summary>
        /// 获取子菜单
        /// </summary>
        /// <param name="ParentId"></param>
        /// <returns></returns>
        //public PagedList<Sys_Menu> GetMenuInfoService(int ParentId)
        //{

        //    string sql = "select ButtonId,ParentId,MenuName,ActionName,Controller,StatusID from dbo.Sys_Menu where ParentId=" + ParentId;
        //    IEnumerable<Sys_Menu> _query = _baseRepository.ExecuteSqlCollection<Sys_Menu>(sql);
        //    return new PagedList<Sys_Menu>(_query, 1, 100, 1);
        //}
        public List<Sys_Menu> GetMenuInfoService(int ParentId)
        {

            string sql = "select ID,ParentId,MenuName,ActionName,Controller,StatusID from Sys_Menu where ParentId=" + ParentId;
            List<Sys_Menu> _query = _baseRepository.ExecuteSqlCollection<Sys_Menu>(sql).ToList();
            return _query;
        }
        /// <summary>
        /// 菜单删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteMenuInfoService(int id)
        {
            string sql = "delete from Sys_Menu where ID=" + id;
            int _query = _baseRepository.ExecuteSql<int>(sql);
            return _query;
        }
        /// <summary>
        /// 菜单新增
        /// </summary>
        /// <param name="Role"></param>
        /// <returns></returns>
        public int AddMenuInfoService(Sys_Menu Menu)
        {
            Menu = WipeNullHelper.WipeNull<Sys_Menu>(Menu);
            Menu.StatusID = 1;
            Menu.MenuImg = "/123.jpg";
            int result = _baseRepository.Insert<Sys_Menu>(Menu);
            if (result > 0)
            {
                LogHelper.Add(new Sys_Log { LogTypeName = "添加", LogName = "添加菜单", UserName = "admin", LogIp = "127.0.0.1", Contents = "添加了一个菜单", CreatTime = DateTime.Now });
            }
            return result;
        }
        /// <summary>
        /// 菜单编辑
        /// </summary>
        /// <param name="Role"></param>
        /// <returns></returns>
        public int UpdateMenuInfoService(Sys_Menu Menu)
        {
            string sql = "select * from Sys_Menu where ID=" + Menu.ID;
            Sys_Menu _query = _baseRepository.ExecuteSqlCollection<Sys_Menu>(sql).FirstOrDefault();
            _query = WipeNullHelper.WipeNull<Sys_Menu>(_query);
            _query.MenuType = Menu.MenuType;
            _query.ParentId = Menu.ParentId;
            _query.MenuName = Menu.MenuName;
            _query.ActionName = Menu.ActionName;
            _query.Controller = Menu.Controller;
            int result = _baseRepository.Update<Sys_Menu>(_query);
            return result;
        }
        public Sys_Menu GetMenuToIdService(int id)
        {
            string sql = "select * from Sys_Menu where  id=" + id;
            Sys_Menu _query = _baseRepository.ExecuteSqlCollection<Sys_Menu>(sql).ToList().FirstOrDefault();
            return _query;
        }
        public List<Sys_Menu> GetMenuService()
        {
            string sql = "select ID,ParentId,MenuName from Sys_Menu";
            List<Sys_Menu> _query = _baseRepository.ExecuteSqlCollection<Sys_Menu>(sql).ToList();
            return _query;
        }

        /// <summary>
        /// 删除菜单
        /// </summary>
        /// <returns></returns>
        public void DelRoleMenu(int rid)
        {
            string sql = " delete from Sys_MenuRoleInfo where rid=" + rid;
            int rulset = _baseRepository.ExecuteSql<int>(sql);

        }
        #endregion

        #region 日志操作
        /// <summary>
        /// 查询日志
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public L.EntityModel.PagedList<L.EntityModel.Sys_Log> GetLogInfoService(ref PagerModel pager, int userid, DetailSearch Smodel)
        {
            var Query = baseRepository2.Search<L.EntityModel.Sys_Log>();

            Query = Query.Where(p => p.UserId == userid);

            if (!string.IsNullOrWhiteSpace(Smodel.UserName))
            {
                Query = Query.Where(p => p.UserName.Contains(Smodel.UserName));
            }
            if (!string.IsNullOrWhiteSpace(Smodel.Contents))
            {
                Query = Query.Where(p => p.Contents.Contains(Smodel.Contents));
            }

            if (Smodel.StartDate != "" && Smodel.StartDate != null)
            {
                DateTime sTime = Convert.ToDateTime(Smodel.StartDate);
                Query = Query.Where(p => p.CreatTime >= sTime);
            }
            if (Smodel.EndDate != "" && Smodel.EndDate != null)
            {
                DateTime eTime = Convert.ToDateTime(Smodel.EndDate);
                Query = Query.Where(p => p.CreatTime <= eTime);
            }
            Query = Query.OrderByDescending(p => p.CreatTime);

            return new L.EntityModel.PagedList<L.EntityModel.Sys_Log>(Query, pager.Page, pager.PageSize);

            #region MyRegion
            //StringBuilder strSql1 = new StringBuilder();
            //strSql1.Append(" ");
            //strSql1.Append("LogTypeName,");
            //strSql1.Append("LogName,");
            //strSql1.Append("UserName,");
            //strSql1.Append("LogIp,");
            //strSql1.Append("Contents,");
            //strSql1.Append("CreatTime,");
            //var strSql = new StringBuilder(strSql1.ToString().Trim(','));
            //pager.FldName = strSql.ToString();

            ////pager.TblName = " Organization_Enterprise e,  Organization_Type t , Sys_Region s ";
            //pager.TblName = " Sys_Log ";

            //string StrCondition = "   1=1 ";

            //if (string.IsNullOrWhiteSpace(pager.StrCondition))
            //{
            //    StrCondition = "   1=1 and UserId=" + userid;

            //}
            //else
            //{
            //    StrCondition = pager.StrCondition;
            //}

            //if (Smodel.UserName != null)
            //{
            //    StrCondition += "  and (UserName like '%" + Smodel.UserName + "%')";
            //}
            //if (Smodel.LogTypeName != null)
            //{
            //    StrCondition += " and (LogTypeName like '%" + Smodel.LogTypeName + "%')";
            //}
            //if (Smodel.Contents != null)
            //{
            //    StrCondition += " and (Contents like '%" + Smodel.Contents + "%')";
            //}
            //if (Smodel.StartDate != "" && Smodel.StartDate != null)
            //{
            //    StrCondition += " and  (left(CreatTime,10) >= '" + Smodel.StartDate + "')";
            //}
            //if (Smodel.EndDate != "" && Smodel.EndDate != null)
            //{
            //    StrCondition += " and  (left(CreatTime,10) <= '" + Smodel.EndDate + "')";
            //}
            //pager.StrCondition = StrCondition;

            //if (string.IsNullOrEmpty(pager.FldSort))
            //    pager.FldSort = " CreatTime desc";
            //MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            //IEnumerable<Sys_Log> resultList = _baseRepository.ExecuteStoredProcedureCollection<Sys_Log>("Common_PageList", parameters);
            //return new PagedList<Sys_Log>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value)); 
            #endregion
        }


        #endregion

        #region 导出查询
        public IEnumerable<L.EntityModel.Sys_Log> GetAllLog(DetailSearch Smodel)
        {
            var Query = baseRepository2.Search<L.EntityModel.Sys_Log>();
            if (string.IsNullOrWhiteSpace(Smodel.UserName))
            {
                Query = Query.Where(p => p.UserName.Contains(Smodel.UserName));
            }
            return Query as IEnumerable<L.EntityModel.Sys_Log>;

            #region MyRegion
            //var rs = "SELECT * FROM `sys_log` ";
            //if (!string.IsNullOrWhiteSpace(Smodel.UserName))
            //{
            //    rs += " where UserName like '%" + Smodel.UserName + "%'";
            //}
            //return _baseRepository.ExecuteSqlCollection<Sys_Log>(rs); 
            #endregion

        }
        #endregion

        #region 收发货地址
        public List<organization_collectsendgoods> GetSendInfoService(int EID, int CollectSendType)
        {
            string sql = @"SELECT
	                    s.*, r1.`name` as ProvinceName,
	                    r2.`name` AS CityName,
	                    r3.`name` AS RegionName
                        FROM
	                    organization_collectsendgoods s
                        LEFT JOIN sys_region r1 ON s.ProvinceId = r1.codeId
                        LEFT JOIN sys_region r2 ON s.City = r2.codeId
                        LEFT JOIN sys_region r3 ON s.Region = r3.codeID
                        where viewdel=0 and EnterpriseId=" + EID + "  and CollectSendType=" + CollectSendType;
            return _baseRepository.ExecuteSqlCollection<organization_collectsendgoods>(sql).ToList();
        }
        public int AddSendInfoService(organization_collectsendgoods send)
        {
            return _baseRepository.Insert<organization_collectsendgoods>(send);
        }
        public int DelSendInfoSerice(int id)
        {
            return baseRepository2.Update(string.Format("UPDATE organization_collectsendgoods SET ViewDel = 1 WHERE ID ={0}", id));

            #region MyRegion
            //string sql = "SELECT * FROM organization_collectsendgoods where  ID=" + id;
            //organization_collectsendgoods send = _baseRepository.ExecuteSqlCollection<organization_collectsendgoods>(sql).ToList().FirstOrDefault();
            //send.ViewDel = 1;
            //return _baseRepository.Update<organization_collectsendgoods>(send); 
            #endregion
        }
        public int EditSendInfoSerice(organization_collectsendgoods send)
        {
            return baseRepository2.Update<L.EntityModel.Organization_CollectSendGoods>(p => p.ID == send.ID, p =>
             {
                 p.Person = send.Person;
                 p.ZipCode = send.ZipCode;
                 p.Mobile = send.Mobile;
                 p.ProvinceId = send.ProvinceId;
                 p.City = send.City;
                 p.Address = send.Address;
                 p.Remark = send.Remark;
             });
            #region MyRegion
            //StringBuilder sb = new StringBuilder();
            //sb.Append("update organization_collectsendgoods set ");
            //sb.Append(" Person=@Person,");
            //sb.Append(" ZipCode=@ZipCode, ");
            //sb.Append(" Mobile=@Mobile, ");
            //sb.Append(" ProvinceId=@ProvinceId, ");
            //sb.Append(" City=@City, ");
            //sb.Append(" Address=@Address, ");
            //sb.Append(" Remark=@Remark  ");
            //sb.Append(" where id=@id");
            //MySqlParameter[] my = { 
            //                    new MySqlParameter("@Person",send.Person),
            //                    new MySqlParameter("@ZipCode",send.ZipCode),
            //                    new MySqlParameter("@Mobile",send.Mobile),
            //                    new MySqlParameter("@ProvinceId",send.ProvinceId),
            //                    new MySqlParameter("@City",send.City),
            //                    new MySqlParameter("@Address",send.Address),
            //                    new MySqlParameter("@Remark",send.Remark)  ,
            //                    new MySqlParameter("@id",send.ID)

            //                      };

            //return _baseRepository.ExecuteSql<int>(sb.ToString(), my); 
            #endregion
        }
        public organization_collectsendgoods GetSendInfoByIDService(int ID)
        {
            string sql = @"SELECT
	                    s.*, r1.`name` as ProvinceName,
	                    r2.`name` AS CityName,
	                    r3.`name` AS RegionName
                        FROM
	                    organization_collectsendgoods s
                        LEFT JOIN sys_region r1 ON s.ProvinceId = r1.codeId
                        LEFT JOIN sys_region r2 ON s.City = r2.codeId
                        LEFT JOIN sys_region r3 ON s.Region = r3.codeID
                        where ID=" + ID;
            return _baseRepository.ExecuteSqlCollection<organization_collectsendgoods>(sql).ToList().FirstOrDefault();
        }
        #endregion
    }
}
